package com.smartdo.crawler.utils;

import cn.hutool.core.date.DateUtil;
import org.apache.commons.lang3.ArrayUtils;

import java.io.Serializable;
import java.sql.*;
import java.util.*;

/**
 * 数据库操作
 */
public class DBUtils implements Serializable {

    private static final long serialVersionUID = 7614285289075969240L;

    private static DBUtils dbUtils;


    public DBUtils() {
    }

    public static DBUtils getDBUtil() {
        if (dbUtils == null) {
            dbUtils = new DBUtils();
        }
        return dbUtils;
    }

    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    /**
     * 关闭资源
     */
    public void close(Connection conn, PreparedStatement ps, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
            }
        }
        if (ps != null) {
            try {
                ps.close();
            } catch (Exception e) {
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
            }
        }
    }

    private void createPs(String sql) throws SQLException {
        conn = getConnection();
        ps = conn.prepareStatement(sql);
    }

    final int batchSize = 1000;

    /**
     * 批量执行
     *
     * @param sql       语句
     * @param paramList 参数列表
     */
    public void executeBatch(String sql, List<List<Object>> paramList) {
        try {
            createPs(sql);
            conn.setAutoCommit(false);
            int count = 0;
            for (List<Object> param : paramList) {
                for (int i = 0; i < param.size(); i++) {
                    ps.setObject(i + 1, param.get(i));
                }
                ps.addBatch();
                if (++count % batchSize == 0) {
                    ps.executeBatch();
                }
            }
            ps.executeBatch();
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(conn, ps, rs);
        }
    }

    static int i = 0;

    /**
     * 执行更新语句
     */
    public int executeUpdate(String sql, Object... param) {
        try {
            System.out.println(DateUtil.now() + ":" + i++);
            createPs(sql);
            setParam(ps, param);
//            String sql1 = ps.toString();
////            System.out.println(sql1);
//            if (sql1.contains(": insert")) {
//                sql1 = sql1.substring(sql1.indexOf(": insert") + 2);
//            } else {
//                sql1 = sql1.substring(sql1.indexOf(": INSERT") + 2);
//            }
//            sql1 = sql1.replace("\n", "\\n");
//            sql1 = sql1 + ";\n";
//            FileUtil.appendString(sql1, "E:\\data\\temp\\sumaitong\\data.sql", "UTF-8");
            return ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(conn, ps, rs);
        }
        return 0;
    }

    private String charEncoding = "UTF-8";

//    public void executeUpdate(String sql, Object... param) {
//        for (int x = 0; x < param.length; x++) {
//            String p = param[x].toString();
//            if (StringUtils.isNotBlank(p)) {
//                p = p.replace("'", "");
//            }
//            sql = sql.replaceFirst("\\?", "'" + p + "'");
//        }
//        sql = sql.replace("\n", "\\n");
//        sql = sql + ";\n";
//        FileUtil.appendString(sql, "E:\\data\\temp\\sumaitong\\data1.sql", "UTF-8");
//        System.out.println(sql);
//    }

    /**
     * 获取链接
     */
    public Connection getConnection() {
        try {
            Class.forName(PropertiesUtil.getPropertie("db_driver"));
            return DriverManager.getConnection(PropertiesUtil.getPropertie("jdbcUrl"), PropertiesUtil.getPropertie("user"), PropertiesUtil.getPropertie("password"));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }


    public Object getObject(String sql, Object... param) {
        Object ob = null;
        try {
            createPs(sql);
            setParam(ps, param);
            rs = ps.executeQuery();
            if (rs.next()) {
                ob = rs.getObject(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(conn, ps, rs);
        }

        return ob;
    }


    /**
     * 查询返回所有结果集 最多返回1000条数据
     */
    public List<Map<String, Object>> getResults(String sql, Object... param) {
        List<Map<String, Object>> results = new Vector<Map<String, Object>>();
        try {
            createPs(sql);
            setParam(ps, param);
            rs = ps.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            String[] columns = null;
            int columnCount = rsmd.getColumnCount();
            for (int x = 0; x < columnCount; x++) {
                columns = ArrayUtils.add(columns, rsmd.getColumnName(x + 1));
            }
            while (rs.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                for (int x = 0; x < columnCount; x++) {
                    map.put(columns[x], rs.getObject(x + 1));
                }
                results.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(conn, ps, rs);
        }
        return results;
    }

    public LinkedList<Map<String, Object>> getResultsToLinked(String sql, Object... param) {
        LinkedList<Map<String, Object>> results = new LinkedList<>();
        try {
            createPs(sql);
            setParam(ps, param);
            rs = ps.executeQuery();
            String[] columns = null;
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            for (int x = 0; x < columnCount; x++) {
                columns = ArrayUtils.add(columns, rsmd.getColumnName(x + 1));
            }

            while (rs.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                for (int x = 0; x < columnCount; x++) {
                    System.out.println("");
                    map.put(columns[x], rs.getObject(x + 1));
                }
                results.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(conn, ps, rs);
        }
        return results;
    }


    public Map<String, Object> getResult(String sql, Object... param) {
        List<Map<String, Object>> results = getResults(sql, param);
        if (results == null || results.size() == 0) {
            return null;
        }
        return results.get(0);
    }

    /**
     * 插入表，如果该表是存在自动增长的列，则返回 自动增长的的数据
     */
    public Integer insertAutoIncrementTable(String sql, Object... param) {
        try {
            conn = getConnection();

            /*执行预处理语句，设置一个自增长列*/
            ps = conn.prepareStatement(sql, 1);

            setParam(ps, param);
            ps.executeUpdate();
            rs = ps.getGeneratedKeys();
            while (rs.next()) {
                return rs.getInt(1);
            }
            return 0;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(conn, ps, rs);
        }
        return null;
    }

    /**
     * 给预处理设置参数
     */
    private void setParam(PreparedStatement ps, Object... param)
            throws SQLException {
        for (int x = 0; x < param.length; x++) {
            if (param[x] instanceof String) {
                param[x] = param[x].toString().replace("'", "’");
            }
            ps.setObject(x + 1, param[x]);
        }
    }

    public Integer uniqueResultCount(String sql, Object... param) {
        try {
            createPs(sql);
            setParam(ps, param);
            rs = ps.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            } else {
                return 0;
            }
        } catch (SQLException e) {
            return 0;
        } finally {
            close(conn, ps, rs);
        }
    }

}
